<%@ Page language="vb" CodeFile="searchVehicleOvertime.aspx.vb" Inherits="searchVehicleOvertime_aspx_vb" %>

<%
    rowNumber = 0
    If (Request.QueryString("do")) = Nothing Then 
        subDo = Request.QueryString("subDo")
        Over()
        dbOpen()
        RecSet = Conn.Execute("Select partnerID, partnerName, countryID From tblPartner Order by partnerName")
        If Not RecSet.EOF Then 
            Do Until RecSet.EOF
                partnerID = RecSet.Fields("partnerID").Value
                partnerName = RecSet.Fields("partnerName").Value
                countryID = RecSet.Fields("countryID").Value
                strSelectSubJS = strSelectSubJS + "new Array(" & partnerID & "," & countryID & ",""" & partnerName & """),"
                RecSet.MoveNext()
            Loop
        End IF
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        dbOpen()
        RecSet = Conn.Execute("Select countryID From tblCountry Order by countryID")
        Do Until RecSet.EOF
            countryID = RecSet.Fields("countryID").Value
            strSelectSubJS = strSelectSubJS + "new Array(0," & countryID & ","" ------------------------------------  ""),"
            RecSet.MoveNext()
        Loop
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        strSelectSubJS = Left(strSelectSubJS, Len(strSelectSubJS) - 1)
        strSelectSubHeadline = "Select partner"
        selectSub()
        Response.Write("<table cellpadding='0' cellspacing='0' border='0' width='100%'>")
        Response.Write("<tr>")
        Response.Write("<td valign='top'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='450'>")
        Response.Write("<tr><td class='headline' colspan='3'>SEARCH LEASE CONTRACTS ON OVERTIME<hr class='hrHeadline'></td></tr>")
        Response.Write("<form action='searchVehicleOverTime.aspx?subDo=search' method='post' name='store_productsSearch'>")
        Response.Write("<tr>")
        Response.Write("<td class='formBold'>Country:</td>")
        Response.Write("<td align='right'>")
        Response.Write("<select name='countryID' id='countryID' onchange=""set_child_listbox(this, document.store_productsSearch.partnerID,Product,'Products');"">")
        Response.Write("<option value=''> Select country  ")
        dbOpen()
        RecSet = Conn.Execute("Select countryID, country From tblCountry")
        If Not RecSet.EOF Then 
            Do Until RecSet.EOF
                countryID = RecSet.Fields("countryID").Value
                country = RecSet.Fields("country").Value
                Response.Write("<option value='" & countryID & "'> " & country & " ")
                RecSet.MoveNext()
            Loop
        End IF
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        Response.Write("<option value=''> -------------------------------------- ")
        Response.Write("</select>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr>")
        Response.Write("<td class='formBold'>Partner:</td>")
        Response.Write("<td align='right'>")
        Response.Write("<select name='partnerID' id='partnerID' disabled>")
        Response.Write("<option value=''> - Select partner - ")
        Response.Write("<option value=''> -------------------------------------- ")
        Response.Write("</select>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr><td align='right' colspan='2'><input type='submit' value='Search'></td></tr>")
        Response.Write("</form>")
        Response.Write("<tr><td colspan='3'><hr class='hrHeadline'></td></tr>")
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("<td width='100'></td>")
        Response.Write("<td valign='top'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='450'>")
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr><td><br></td></tr>")
        countryID = Request.Form("countryID")
        partnerID = Request.Form("partnerID")
        If Len(countryID) = 0 And Len(partnerID) = 0 Then 
            countryID = Request.QueryString("countryID")
            partnerID = Request.QueryString("partnerID")
        End IF
        If Len(countryID) > 0 Then 
            countryID = CLng(countryID)
            sqlCountryID = " AND v.countryID = " & countryID & ""
        End IF
        If Len(partnerID) > 0 Then 
            partnerID = CLng(partnerID)
            sqlPartnerID = " AND v.partnerID = " & partnerID & ""
        End IF
        If Len(subDo) = 0 Then 
            sqlCountryID = " And v.countryID = 1000"
        End IF
        Response.Write("<tr>")
        Response.Write("<td valign='top' colspan='3'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='100%'>")
        Response.Write("<tr>")
        Response.Write("<td class='headline' colspan='11'>")
        Response.Write("SEARCH RESULT")
        Response.Write("<hr class='hrHeadline'>")
        Response.Write("</td>")
        Response.Write("</tr>")
        dbOpen()
        RecSet = Conn.Execute("Select v.vehicleID, v.regnr, v.vinNumber, v.contractNumber, v.leaseStartDate, p.product, pl.priceListDuration, b.brand, m.model, d.dealer, c.country, (Select Count(vehicleID) From tblInvoiceContent Where vehicleID = v.vehicleID) As vehicleCount From ((((((tblVehicle v INNER JOIN tblProduct p ON p.productID = v.productID) INNER JOIN tblPriceList pl ON pl.priceListID = v.priceListID) INNER JOIN tblBrand b ON b.brandID = v.brandID) INNER JOIN tblModel m ON m.modelID = v.modelID) INNER JOIN tblDealer d ON d.dealerID = v.dealerID) INNER JOIN tblCountry c ON c.countryID = v.countryID) Where (pl.priceListDuration+1) <= ((Select Count(vehicleID) From tblInvoiceContent ic2 Where ic2.vehicleID = v.vehicleID)-(Select Count(vehicleID) From tblInvoiceCreditContent ic3 Where ic3.vehicleID = v.vehicleID)) And v.completed = 0 And v.active = 1 And v.leaseEndDate Is Null" & sqlPartnerID & sqlCountryID & "")
        If RecSet.EOF Then 
            Response.Write("<tr><td><font color='red'>No lease contracts found.</font></td></tr>")
        Else
            Response.Write("<tr bgcolor='#eeeeee'>")
            Response.Write("<td class='formBold'>Car dealer</td>")
            Response.Write("<td class='formBold'>Contract number</td>")
            Response.Write("<td class='formBold'>Licence number</td>")
            Response.Write("<td class='formBold'>VIN-number</td>")
            Response.Write("<td class='formBold'>Car make</td>")
            Response.Write("<td class='formBold'>Product</td>")
            Response.Write("<td class='formBold'>Country</td>")
            Response.Write("<td class='formBold'>Lease start date</td>")
            Response.Write("<td class='formBold'>Lease end date</td>")
            Response.Write("<td class='formBold'>Times invoiced</td>")
            Response.Write("<td class='formBold'></td>")
            Response.Write("</tr>")
            rowNumber = 1
            Do Until RecSet.EOF
                vehicleID = RecSet.Fields("vehicleID").Value
                regnr = RecSet.Fields("regnr").Value
                vinNumber = RecSet.Fields("vinNumber").Value
                contractNumber = RecSet.Fields("contractNumber").Value
                leaseStartDate = RecSet.Fields("leaseStartDate").Value
                product = RecSet.Fields("product").Value
                priceListDuration = RecSet.Fields("priceListDuration").Value
                brand = RecSet.Fields("brand").Value
                model = RecSet.Fields("model").Value
                dealer = RecSet.Fields("dealer").Value
                country = RecSet.Fields("country").Value
                vehicleCount = RecSet.Fields("vehicleCount").Value
                leaseEndDate = DateAdd("m", CDbl(priceListDuration), Convert.ToDateTime(leaseStartDate))
                If Right(leaseStartDate, 2) <> "01" Then 
                    leaseEndDate = DateAdd("m", CDbl("1"), Convert.ToDateTime(leaseEndDate))
                End IF
                leaseEndDate = Left(leaseEndDate, 8) & "01"
                leaseEndDate = DateAdd("d", CDbl("-1"), Convert.ToDateTime(leaseEndDate))
                If rowNumber = 0 Then 
                    strGrey = " bgcolor='#eeeeee'"
                    rowNumber = 1
                Else
                    strGrey = ""
                    rowNumber = 0
                End IF
                Response.Write("<tr" & strGrey & ">")
                Response.Write("<td>" & dealer & "</td>")
                Response.Write("<td>" & contractNumber & "</td>")
                Response.Write("<td>" & regnr & "</td>")
                Response.Write("<td>" & vinNumber & "</td>")
                Response.Write("<td>" & brand & " " & model & "</td>")
                Response.Write("<td>" & product & "</td>")
                Response.Write("<td>" & country & "</td>")
                Response.Write("<td>" & leaseStartDate & "</td>")
                Response.Write("<td>" & leaseEndDate & "</td>")
                Response.Write("<td>" & vehicleCount & "/" & priceListDuration & "</td>")
                Response.Write("<td><a href='editVehicle.aspx?vehicleID=" & vehicleID & "'>view details</a></td>")
                Response.Write("</tr>")
                RecSet.MoveNext()
            Loop
        End IF
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        Response.Write("<tr><td colspan='11'><hr class='hrHeadline'></td></tr>")
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("</table>")
        Under()
    End IF
%>
